[sqlalchemy] subquery in select statement

Posted by webjunkie on Stack Overflow See other posts from Stack Overflow or by webjunkie
Published on 2010-12-31T01:48:03Z Indexed on 2010/12/31 1:54 UTC
Read the original article Hit count: 153

Filed under:

sqlalchemy

|

pylons

Hi guys, I have two tables (albums,pictures) in a one to many relationship and I want to display each albums details with one picture so I have the following query

select albums.name,(select pictures.path from pictures where pictures.albumid=albums.id limit 1) as picture from albums where ...

Now I'm struggling creating this on Pylons with sqlalchemy I tried to do the following

picture = Session.query(model.Picture)

sub_q = picture.filter_by(albumid = model.Album.id).limit(1).subquery()

album_q = Session.query(model.Album, sub_q)

result = album_q.all()

but it creates the following statement displaying the incorrect picture beacuse the table albums is included in the subquery

select albums.name,(select pictures.path from pictures,albums where pictures.albumid=albums.id) from albums where ...

Am I doing it wrong?, is this even possible in sqlalchemy?.

© Stack Overflow or respective owner

Related posts about sqlalchemy

sqlalchemy date type in 0.6 migration using mssql

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm connection to mssql server through pyodbc, via FreeTDS odbc driver, on linux ubuntu 10.04. Sqlalchemy 0.5 uses DATETIME for sqlalchemy.Date() fields. Now Sqlalchemy 0.6 uses DATE, but sql server 2000 doesn't have a DATE type. How can I make DATETIME be the default for sqlalchemy.Date() on sqlalchemy… >>> More
OperationalError "unable to open database file" processing query results with SQLAlchemy and SQLite3

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm running into this little problem that I hope is just a dumb user error. It looks like some sort of a size limit with a query to a SQLite database. I managed to reproduce the issue with an in-memory DB and a simple script shown below. I can make it work by either reducing the number of records… >>> More
Starter question of declarative style SQLAlchemy relation()

as seen on Stack Overflow - Search for 'Stack Overflow'
I am quite new to SQLAlchemy, or even database programming, maybe my question is too simple. Now I have two class/table: class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(40)) ... class Computer(Base): __tablename__ = 'comps' … >>> More
Default class for SQLAlchemy single table inheritance

as seen on Stack Overflow - Search for 'Stack Overflow'
I've set up a single table inheritance, but I need a "default" class to use when an unknown polymorphic identity is encountered. The database is not in my control and so the data can be pretty much anything. A working example setup: import sqlalchemy as sa from sqlalchemy import orm engine = sa… >>> More
Batch select with SQLAlchemy

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a large set of values V, some of which are likely to exist in a table T. I would like to insert into the table those which are not yet inserted. So far I have the code: for value in values: s = self.conn.execute(mytable.__table__.select(mytable.value == value)).first() … >>> More

Related posts about pylons

Logging in worker threads spawned from a pylons application does not seem to work

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a pylons application where, under certain cirumstances I want to spawn multiple worker threads to process items in a queue. Right now we aren't making use of a ThreadPool (would be ideal, but we'll add that in later). The main problem is that the worker threads logging does not get written… >>> More
Pylons FormEncode @validate decorator pass parameters into re-render action

as seen on Stack Overflow - Search for 'Stack Overflow'
I am attempting to use the validate decorator in Pylons with FormEncode and I have encountered an issue. I am attempting to validate a form on a controller action that requires parameters, and if the validation fails, the parameters aren't passed back in when the form is re-rendered. Here's an example… >>> More
How to set cookies with redirect in Pylons

as seen on Stack Overflow - Search for 'Stack Overflow'
In light of the cookie-handling bugs affecting Safari and Chrome (see this thread), and Pylons implementation of redirect_to as an exception, is it possible to reliably set a tracking cookie and redirect at the same time? Is the META refresh method looked down upon? >>> More
Disable browser caching in pylons

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm have an action /json that returns json from the server. Unfortunately in IE, the browser likes to cache this json. How can I make it so that this action doesn't cache? >>> More
Deploying Pylons with Nginx reverse proxy?

as seen on Stack Overflow - Search for 'Stack Overflow'
Is there a tutorial on how to deploy Pylons with Nginx? I've been able to start nginx and then serve pylons to :8080 with paster serve development.ini However, I can't seem to do other stuff as pylons locks me into that serve mode. If I try to CTRL+Z out of pylons serving to do other stuff on my… >>> More